﻿CREATE TABLE Diametre
(
    Code varchar(5) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_Diametre PRIMARY KEY (Code)
);

CREATE TABLE Domaine
(
    Code varchar(3) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_Domaine PRIMARY KEY (Code)
);

CREATE TABLE Emplacement
(
    Code varchar(6) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_Emplacement PRIMARY KEY (Code)
);

CREATE TABLE Materiau
(
    Code varchar(5) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_Materiau PRIMARY KEY (Code)
);

CREATE TABLE RangEquipement
(
    Code varchar(5) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_RangEquipement PRIMARY KEY (Code)
);

CREATE TABLE Statut
(
    Code varchar(5) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_Statut PRIMARY KEY (Code)
);

CREATE TABLE StatutConformite
(
    Code varchar(5) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_StatutConformite PRIMARY KEY (Code)
);

CREATE TABLE StatutConvention
(
    Code varchar(5) NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_StatutConvention PRIMARY KEY (Code)
);

CREATE TABLE Bordereaux
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    CodePharos varchar(12) NOT NULL,
    Libelle varchar(127) NOT NULL,
    PrixUnitaire varchar(MAX) NOT NULL,
    CONSTRAINT PK_Bordereaux PRIMARY KEY (Id)
);

CREATE TABLE Communes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Nom varchar(63),
    CodePostal varchar(5),
    CodeInsee varchar(5),
    Actif bit NOT NULL,
    CONSTRAINT PK_Communes PRIMARY KEY (Id)
);

CREATE TABLE Contrats
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    TypeContrat varchar(MAX),
    Nom varchar(63),
    Domaine varchar(MAX),
    Actif bit NOT NULL,
    CONSTRAINT PK_Contrats PRIMARY KEY (Id)
);

CREATE TABLE Documents
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Nom varchar(127),
    Donnees varchar(MAX),
    Fichier varchar(MAX),
    CONSTRAINT PK_Documents PRIMARY KEY (Id)
);

CREATE TABLE Programmes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Nom varchar(MAX),
    TypeProgramme varchar(MAX) NOT NULL,
    DateOuverture datetime,
    Delai datetime,
    DateCloture datetime,
    Statut_Code varchar(5) NOT NULL,
    Commentaire varchar(255),
    CONSTRAINT PK_Programmes PRIMARY KEY (Id)
);
ALTER TABLE Programmes ADD CONSTRAINT FK_Programmes_Statut FOREIGN KEY (Statut_Code) REFERENCES Statut(Code);
CREATE INDEX FK_Programmes_Statut ON Programmes(Statut_Code);

CREATE TABLE Voies
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Commune_Id uniqueidentifier NOT NULL,
    Nom varchar(63),
    CONSTRAINT PK_Voies PRIMARY KEY (Id)
);
ALTER TABLE Voies ADD CONSTRAINT FK_Voies_Communes FOREIGN KEY (Commune_Id) REFERENCES Communes(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Voies_Communes ON Voies(Commune_Id);

CREATE TABLE Adresses
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Voie_Id uniqueidentifier NOT NULL,
    Numero varchar(7),
    Complement varchar(255),
    CONSTRAINT PK_Adresses PRIMARY KEY (Id)
);
ALTER TABLE Adresses ADD CONSTRAINT FK_Adresses_Voies FOREIGN KEY (Voie_Id) REFERENCES Voies(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Adresses_Voies ON Adresses(Voie_Id);

CREATE TABLE Personnes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Adresse_Id uniqueidentifier,
    Nom varchar(63) NOT NULL,
    Titre varchar(20),
    TphFixe varchar(25),
    TphPortable varchar(25),
    TphBureau varchar(25),
    Fax varchar(25),
    Fax2 varchar(25),
    Mail varchar(127),
    Actif bit NOT NULL,
    CONSTRAINT PK_Personnes PRIMARY KEY (Id)
);
ALTER TABLE Personnes ADD CONSTRAINT FK_Personnes_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_Personnes_Adresses ON Personnes(Adresse_Id);

CREATE TABLE LieuxDeService
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Client_Id uniqueidentifier,
    Adresse_Id uniqueidentifier,
    Statut_Code varchar(5) NOT NULL,
    Risque varchar(MAX) NOT NULL,
    Localisation varchar(255),
    Actif bit NOT NULL,
    CONSTRAINT PK_LieuxDeService PRIMARY KEY (Id)
);
ALTER TABLE LieuxDeService ADD CONSTRAINT FK_LieuxDeService_Personnes FOREIGN KEY (Client_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_LieuxDeService_Personnes ON LieuxDeService(Client_Id);
ALTER TABLE LieuxDeService ADD CONSTRAINT FK_LieuxDeService_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_LieuxDeService_Adresses ON LieuxDeService(Adresse_Id);
ALTER TABLE LieuxDeService ADD CONSTRAINT FK_LieuxDeService_StatutConformite FOREIGN KEY (Statut_Code) REFERENCES StatutConformite(Code);
CREATE INDEX FK_LieuxDeService_StatutConformite ON LieuxDeService(Statut_Code);

CREATE TABLE Dossiers
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    LieuDeService_Id uniqueidentifier NOT NULL,
    Programme_Id uniqueidentifier,
    TypeDossier varchar(MAX),
    DateOuverture datetime,
    Delai datetime,
    DateCloture datetime,
    Statut_Code varchar(5) NOT NULL,
    Commentaire varchar(MAX),
    CONSTRAINT PK_Dossiers PRIMARY KEY (Id)
);
ALTER TABLE Dossiers ADD CONSTRAINT FK_Dossiers_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Dossiers_LieuxDeService ON Dossiers(LieuDeService_Id);
ALTER TABLE Dossiers ADD CONSTRAINT FK_Dossiers_Programmes FOREIGN KEY (Programme_Id) REFERENCES Programmes(Id) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE INDEX FK_Dossiers_Programmes ON Dossiers(Programme_Id);
ALTER TABLE Dossiers ADD CONSTRAINT FK_Dossiers_Statut FOREIGN KEY (Statut_Code) REFERENCES Statut(Code);
CREATE INDEX FK_Dossiers_Statut ON Dossiers(Statut_Code);

CREATE TABLE Etapes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Dossier_Id uniqueidentifier NOT NULL,
    TypeEtape varchar(MAX) NOT NULL,
    DatePlanification datetime,
    DateOuverture datetime,
    Delai datetime,
    DateRealisation datetime,
    Statut_Code varchar(5) NOT NULL,
    Commentaire varchar(255),
    CONSTRAINT PK_Etapes PRIMARY KEY (Id)
);
ALTER TABLE Etapes ADD CONSTRAINT FK_Etapes_Dossiers FOREIGN KEY (Dossier_Id) REFERENCES Dossiers(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Etapes_Dossiers ON Etapes(Dossier_Id);
ALTER TABLE Etapes ADD CONSTRAINT FK_Etapes_Statut FOREIGN KEY (Statut_Code) REFERENCES Statut(Code);
CREATE INDEX FK_Etapes_Statut ON Etapes(Statut_Code);

CREATE TABLE Actions
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Etape_Id uniqueidentifier NOT NULL,
    TypeAction varchar(MAX) NOT NULL,
    DatePlanification datetime,
    DateOuverture datetime,
    Acteur varchar(68),
    Delai datetime,
    DateRealisation datetime,
    Statut_Code varchar(5) NOT NULL,
    Commentaire varchar(255),
    CONSTRAINT PK_Actions PRIMARY KEY (Id)
);
ALTER TABLE Actions ADD CONSTRAINT FK_Actions_Etapes FOREIGN KEY (Etape_Id) REFERENCES Etapes(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Actions_Etapes ON Actions(Etape_Id);
ALTER TABLE Actions ADD CONSTRAINT FK_Actions_Statut FOREIGN KEY (Statut_Code) REFERENCES Statut(Code);
CREATE INDEX FK_Actions_Statut ON Actions(Statut_Code);

CREATE TABLE Branchements
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Personne_Id uniqueidentifier,
    Adresse_Id uniqueidentifier,
    CodeGn varchar(14),
    Materiau_Code varchar(5),
    Diametre_Code varchar(5),
    Emplacement_Code varchar(6),
    Localisation varchar(255),
    Actif bit NOT NULL,
    CONSTRAINT PK_Branchements PRIMARY KEY (Id)
);
ALTER TABLE Branchements ADD CONSTRAINT FK_Branchements_Personnes FOREIGN KEY (Personne_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_Branchements_Personnes ON Branchements(Personne_Id);
ALTER TABLE Branchements ADD CONSTRAINT FK_Branchements_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_Branchements_Adresses ON Branchements(Adresse_Id);
ALTER TABLE Branchements ADD CONSTRAINT FK_Branchements_Materiau FOREIGN KEY (Materiau_Code) REFERENCES Materiau(Code) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE INDEX FK_Branchements_Materiau ON Branchements(Materiau_Code);
ALTER TABLE Branchements ADD CONSTRAINT FK_Branchements_Diametre FOREIGN KEY (Diametre_Code) REFERENCES Diametre(Code) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE INDEX FK_Branchements_Diametre ON Branchements(Diametre_Code);
ALTER TABLE Branchements ADD CONSTRAINT FK_Branchements_Emplacement FOREIGN KEY (Emplacement_Code) REFERENCES Emplacement(Code) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE INDEX FK_Branchements_Emplacement ON Branchements(Emplacement_Code);

CREATE TABLE Compteurs
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    LieuDeService_Id uniqueidentifier NOT NULL,
    Branchement_Id uniqueidentifier NOT NULL,
    Adresse_Id uniqueidentifier,
    CodeGn varchar(14),
    Numero varchar(15),
    Diametre_Code varchar(5),
    Marque varchar(31),
    Modele varchar(31),
    Localisation varchar(255),
    Actif bit NOT NULL,
    CONSTRAINT PK_Compteurs PRIMARY KEY (Id)
);
ALTER TABLE Compteurs ADD CONSTRAINT FK_Compteurs_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id);
CREATE INDEX FK_Compteurs_LieuxDeService ON Compteurs(LieuDeService_Id);
ALTER TABLE Compteurs ADD CONSTRAINT FK_Compteurs_Branchements FOREIGN KEY (Branchement_Id) REFERENCES Branchements(Id);
CREATE INDEX FK_Compteurs_Branchements ON Compteurs(Branchement_Id);
ALTER TABLE Compteurs ADD CONSTRAINT FK_Compteurs_Adresses FOREIGN KEY (Adresse_Id) REFERENCES Adresses(Id);
CREATE INDEX FK_Compteurs_Adresses ON Compteurs(Adresse_Id);
ALTER TABLE Compteurs ADD CONSTRAINT FK_Compteurs_Diametre FOREIGN KEY (Diametre_Code) REFERENCES Diametre(Code) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE INDEX FK_Compteurs_Diametre ON Compteurs(Diametre_Code);

CREATE TABLE Equipements
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Compteur_Id uniqueidentifier NOT NULL,
    TypeEquipement varchar(MAX),
    Statut varchar(MAX),
    Diametre_Code varchar(5),
    Marque varchar(31),
    Modele varchar(31),
    Numero varchar(15),
    Rang_Code varchar(5) NOT NULL,
    DatePose datetime,
    Localisation varchar(255),
    Conventionne bit,
    Actif bit NOT NULL,
    CONSTRAINT PK_Equipements PRIMARY KEY (Id)
);
ALTER TABLE Equipements ADD CONSTRAINT FK_Equipements_Compteurs FOREIGN KEY (Compteur_Id) REFERENCES Compteurs(Id);
CREATE INDEX FK_Equipements_Compteurs ON Equipements(Compteur_Id);
ALTER TABLE Equipements ADD CONSTRAINT FK_Equipements_Diametre FOREIGN KEY (Diametre_Code) REFERENCES Diametre(Code) ON DELETE SET NULL ON UPDATE SET NULL;
CREATE INDEX FK_Equipements_Diametre ON Equipements(Diametre_Code);
ALTER TABLE Equipements ADD CONSTRAINT FK_Equipements_RangEquipement FOREIGN KEY (Rang_Code) REFERENCES RangEquipement(Code);
CREATE INDEX FK_Equipements_RangEquipement ON Equipements(Rang_Code);

CREATE TABLE Contacts
(
    Personne_Id uniqueidentifier NOT NULL,
    LieuDeService_Id uniqueidentifier NOT NULL,
    Description varchar(255),
    CONSTRAINT PK_Contacts PRIMARY KEY (Personne_Id, LieuDeService_Id)
);
ALTER TABLE Contacts ADD CONSTRAINT FK_Contacts_Personnes FOREIGN KEY (Personne_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_Contacts_Personnes ON Contacts(Personne_Id);
ALTER TABLE Contacts ADD CONSTRAINT FK_Contacts_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id);
CREATE INDEX FK_Contacts_LieuxDeService ON Contacts(LieuDeService_Id);

CREATE TABLE Conventions
(
    Id uniqueidentifier NOT NULL,
    LieuDeService_Id uniqueidentifier NOT NULL,
    Gestionnaire_Id uniqueidentifier NOT NULL,
    Statut_Code varchar(5) NOT NULL,
    Numero varchar(8) NOT NULL,
    DateSignature datetime,
    Montant int,
    Remise int,
    Actif varchar(MAX) NOT NULL,
    CONSTRAINT UN_Conventions_Numero PRIMARY KEY (Numero)
);
ALTER TABLE Conventions ADD CONSTRAINT FK_Conventions_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Conventions_LieuxDeService ON Conventions(LieuDeService_Id);
ALTER TABLE Conventions ADD CONSTRAINT FK_Conventions_Personnes FOREIGN KEY (Gestionnaire_Id) REFERENCES Personnes(Id);
CREATE INDEX FK_Conventions_Personnes ON Conventions(Gestionnaire_Id);
ALTER TABLE Conventions ADD CONSTRAINT FK_Conventions_StatutConvention FOREIGN KEY (Statut_Code) REFERENCES StatutConvention(Code);
CREATE INDEX FK_Conventions_StatutConvention ON Conventions(Statut_Code);

CREATE TABLE Interventions
(
    Id uniqueidentifier NOT NULL,
    Equipement_Id uniqueidentifier NOT NULL,
    CONSTRAINT PK_Interventions PRIMARY KEY (Id)
);
ALTER TABLE Interventions ADD CONSTRAINT FK_Interventions_Actions FOREIGN KEY (Id) REFERENCES Actions(Id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE Interventions ADD CONSTRAINT FK_Interventions_Equipements FOREIGN KEY (Equipement_Id) REFERENCES Equipements(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Interventions_Equipements ON Interventions(Equipement_Id);

CREATE TABLE Volumes
(
    Id uniqueidentifier NOT NULL DEFAULT NEWSEQUENTIALID(),
    Compteur_Id uniqueidentifier NOT NULL,
    DateValeur datetime,
    Valeur bigint,
    CONSTRAINT PK_Volumes PRIMARY KEY (Id)
);
ALTER TABLE Volumes ADD CONSTRAINT FK_Volumes_Compteurs FOREIGN KEY (Compteur_Id) REFERENCES Compteurs(Id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX FK_Volumes_Compteurs ON Volumes(Compteur_Id);

CREATE TABLE ActionsDocuments
(
    Action_Id uniqueidentifier NOT NULL,
    Document_Id uniqueidentifier NOT NULL,
    CONSTRAINT PK_ActionsDocuments PRIMARY KEY (Action_Id, Document_Id)
);
ALTER TABLE ActionsDocuments ADD CONSTRAINT FK_ActionsDocuments_Actions FOREIGN KEY (Action_Id) REFERENCES Actions(Id);
CREATE INDEX FK_ActionsDocuments_Actions ON ActionsDocuments(Action_Id);
ALTER TABLE ActionsDocuments ADD CONSTRAINT FK_ActionsDocuments_Documents FOREIGN KEY (Document_Id) REFERENCES Documents(Id);
CREATE INDEX FK_ActionsDocuments_Documents ON ActionsDocuments(Document_Id);

CREATE TABLE LieuxDeServiceContrats
(
    LieuDeService_Id uniqueidentifier NOT NULL,
    Contrat_Id uniqueidentifier NOT NULL,
    CONSTRAINT PK_LieuxDeServiceContrats PRIMARY KEY (LieuDeService_Id, Contrat_Id)
);
ALTER TABLE LieuxDeServiceContrats ADD CONSTRAINT FK_LieuxDeServiceContrats_LieuxDeService FOREIGN KEY (LieuDeService_Id) REFERENCES LieuxDeService(Id);
CREATE INDEX FK_LieuxDeServiceContrats_LieuxDeService ON LieuxDeServiceContrats(LieuDeService_Id);
ALTER TABLE LieuxDeServiceContrats ADD CONSTRAINT FK_LieuxDeServiceContrats_Contrats FOREIGN KEY (Contrat_Id) REFERENCES Contrats(Id);
CREATE INDEX FK_LieuxDeServiceContrats_Contrats ON LieuxDeServiceContrats(Contrat_Id);

GO

INSERT INTO Diametre VALUES('00012', '');
INSERT INTO Diametre VALUES('00015', '');
INSERT INTO Diametre VALUES('00020', '');
INSERT INTO Diametre VALUES('00025', '');
INSERT INTO Diametre VALUES('00027', '');
INSERT INTO Diametre VALUES('00030', '');
INSERT INTO Diametre VALUES('00032', '');
INSERT INTO Diametre VALUES('00040', '');
INSERT INTO Diametre VALUES('00050', '');
INSERT INTO Diametre VALUES('00060', '');
INSERT INTO Diametre VALUES('00063', '');
INSERT INTO Diametre VALUES('00065', '');
INSERT INTO Diametre VALUES('00080', '');
INSERT INTO Diametre VALUES('00100', '');
INSERT INTO Diametre VALUES('00150', '');
INSERT INTO Diametre VALUES('00200', '');
INSERT INTO Diametre VALUES('00250', '');
INSERT INTO Diametre VALUES('00400', '');
INSERT INTO Domaine VALUES('EAU', '');
INSERT INTO Domaine VALUES('ASS', '');
INSERT INTO Emplacement VALUES('STROPR', '');
INSERT INTO Emplacement VALUES('STROIM', '');
INSERT INTO Emplacement VALUES('SCPRPR', '');
INSERT INTO Emplacement VALUES('SCPRIM', '');
INSERT INTO Emplacement VALUES('SCPUPR', '');
INSERT INTO Emplacement VALUES('SCPUIM', '');
INSERT INTO Emplacement VALUES('SESPVT', '');
INSERT INTO Emplacement VALUES('COUR', '');
INSERT INTO Materiau VALUES('PLOMB', '');
INSERT INTO Materiau VALUES('PPEHD', '');
INSERT INTO Materiau VALUES('PVC', '');
INSERT INTO Materiau VALUES('ACFER', '');
INSERT INTO Materiau VALUES('FONTE', '');
INSERT INTO Materiau VALUES('CUIVR', '');
INSERT INTO Materiau VALUES('FONGR', '');
INSERT INTO Materiau VALUES('AMICT', '');
INSERT INTO Materiau VALUES('BETON', '');
INSERT INTO Materiau VALUES('GRES', '');
INSERT INTO Materiau VALUES('BUSCT', '');
INSERT INTO Materiau VALUES('POLYL', '');
INSERT INTO Materiau VALUES('TRICO', '');
INSERT INTO RangEquipement VALUES('PREMR', '');
INSERT INTO RangEquipement VALUES('SECND', '');
INSERT INTO Statut VALUES('AOUVR', '');
INSERT INTO Statut VALUES('NONOU', '');
INSERT INTO Statut VALUES('ENCOU', '');
INSERT INTO Statut VALUES('CLOTR', '');
INSERT INTO Statut VALUES('ANNUL', '');
INSERT INTO Statut VALUES('AREPL', '');
INSERT INTO Statut VALUES('AREAF', '');
INSERT INTO StatutConformite VALUES('PTNCF', '');
INSERT INTO StatutConformite VALUES('NCFRM', '');
INSERT INTO StatutConformite VALUES('NCFPH', '');
INSERT INTO StatutConformite VALUES('NCFRG', '');
INSERT INTO StatutConformite VALUES('CFRME', '');
INSERT INTO StatutConvention VALUES('PROPO', '');
INSERT INTO StatutConvention VALUES('ACCPT', '');
INSERT INTO StatutConvention VALUES('REFUS', '');
INSERT INTO StatutConvention VALUES('RESIL', '');

GO

